package com.kamikaze;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class Overtime {
	String user_id;
	String otd_date;
	String otd_time_begin;
	String otd_time_end;
	String ot_number;
	String checker_id;
	String leave_note;
	int shift_number = 0;
	ArrayList<String> errors = new ArrayList<String>();

	public Overtime() {
	}

	public String getUser_id() {
		return user_id;
	}

	public void setUser_id(String user_id) {
		this.user_id = user_id;
	}

	public String getOtd_date() {
		return otd_date;
	}

	public void setOtd_date(String otd_date) {
		this.otd_date = otd_date;
	}

	public String getOtd_time_begin() {
		return otd_time_begin;
	}

	public void setOtd_time_begin(String otd_time_begin) {
		this.otd_time_begin = otd_time_begin;
	}

	public String getOtd_time_end() {
		return otd_time_end;
	}

	public void setOtd_time_end(String otd_time_end) {
		this.otd_time_end = otd_time_end;
	}

	public String getOt_number() {
		return ot_number;
	}

	public void setOt_number(String ot_number) {
		this.ot_number = ot_number;
	}

	public String getChecker_id() {
		return checker_id;
	}

	public void setChecker_id(String checker_id) {
		this.checker_id = checker_id;
	}

	public String getLeave_note() {
		return leave_note;
	}

	public void setLeave_note(String leave_note) {
		this.leave_note = leave_note;
	}

	public int getShift_number(String user_id,String otd_date) {
		String year = otd_date.substring(0, 4);
		String month = otd_date.substring(5, 7);
		String day = otd_date.substring(8);
		String beginday = year + month + day;
		System.out.print(beginday);
		String get_sfNo = "select `shift_number` from `shift` where `user_id`=? and date(`shift_date`)=?";

		// 取得shift_number
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection con = DriverManager
					.getConnection("jdbc:mysql://j.snpy.org/j2?user=team2&password=kamikaze");
			PreparedStatement pstat = con.prepareStatement(get_sfNo);
			pstat.setString(1, user_id);
			pstat.setString(2, beginday);
			ResultSet rs = pstat.executeQuery();
			if (rs.next()) {
				shift_number = rs.getInt("shift_number");

				System.out.println("java shift_number" + shift_number);
			} else {
				errors.add("你可能是隻菜鳥,因為你沒班表");
			}
			rs.close();
			pstat.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		return shift_number;
	}

	public void setShift_number(int shift_number) {
		this.shift_number = shift_number;
	}

	public ArrayList<String> getErrors() {
		return errors;
	}

	public void setErrors(ArrayList<String> errors) {
		this.errors = errors;
	}
	public boolean check(int shift_number,String otd_date){
		boolean pass = true;
		String year_b = otd_date.substring(0, 4);
		String month_b = otd_date.substring(5, 7);
		Date date = new Date();
		DateFormat df = new SimpleDateFormat("yyyy-MM");
		String now_date = df.format(date);
		String year_now = now_date.substring(0, 4);
		String month_now = now_date.substring(5, 7);
		
		if(!year_b.equals(year_now)){pass=false;errors.add("只能申請今年度加班");}
		if(!month_b.equals(month_now)){pass=false;errors.add("只能申請本月加班");}
		String check_bd = "select * from overtimedata where shift_number = ?";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		Connection con2 = DriverManager
				.getConnection("jdbc:mysql://j.snpy.org/j2?user=team2&password=kamikaze&useUnicode=true&characterEncoding=UTF-8");
		PreparedStatement pstat3 = con2.prepareStatement(check_bd);
		pstat3.setInt(1, shift_number);
		ResultSet rsc = pstat3.executeQuery();
		if(rsc.next()){pass = false; errors.add("加班日期重覆"); }
		rsc.close();
		pstat3.close();
		con2.close();
		} catch (ClassNotFoundException e1) {e1.printStackTrace();}
		catch (SQLException e) {e.printStackTrace();}
		
		return pass;	
	}
	
	public boolean process(String user_id, String otd_date,String otd_time_begin,
			String otd_time_end, String ot_number, String checker_id,
			String otd_note, int shift_number) {
		boolean succ = false;
		String year = otd_date.substring(0, 4);
		String month = otd_date.substring(5, 7);
		String day = otd_date.substring(8);
		String beginday = year + month + day;
	
// 寫入overtimedata
	
			String insert_leave = "INSERT INTO `j2`.`overtimedata` "
					+ "( `shift_number`, `otd_date`, `otd_time_begin`, `otd_time_end`, `ot_number`, `checker_id`,  `otd_note`) "
					+ "VALUES (?,?,?,?,?,?,?);";

			Connection con1;
			try {
				Class.forName("com.mysql.jdbc.Driver");
				con1 = DriverManager
						.getConnection("jdbc:mysql://j.snpy.org/j2?user=team2&password=kamikaze&useUnicode=true&characterEncoding=UTF-8");
				PreparedStatement pstat2 = con1.prepareStatement(insert_leave);
				pstat2.setInt(1, shift_number);
				pstat2.setString(2, beginday);
				pstat2.setString(3, otd_time_begin);
				pstat2.setString(4, otd_time_end);
				pstat2.setString(5, ot_number);
				pstat2.setString(6, checker_id);
				pstat2.setString(7, otd_note);
				int row = pstat2.executeUpdate();
				System.out.println("增加" + row + "筆加班資料");
				if (row != 0) {
					succ = true;errors.add("新增成功");
				}
				pstat2.close();
				con1.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
	
		
		return succ;
	}
	
	
}
